﻿Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.VisualStudio.DebuggerVisualizers
Imports System.Windows.Forms
Imports System.Data.Common
Imports System.Drawing
Imports System.IO

<Assembly: System.Diagnostics.DebuggerVisualizer(GetType(Visualizers.DBCommandVisualizer), GetType(Visualizers.DBcommandVisualizerSource), Target:=GetType(System.Data.Common.DbCommand), Description:="Visualize as a TSQL Script")> 
Namespace Visualizers
    Public Class DBCommandVisualizer
        Inherits DialogDebuggerVisualizer

        Protected Overrides Sub Show(windowService As IDialogVisualizerService, objectProvider As IVisualizerObjectProvider)
            Dim dbc As String = New StreamReader(objectProvider.GetData()).ReadToEnd
            Using form As New Form()
                form.Text = "Command Visualizer"
                form.ClientSize = New Size(400, 300)
                form.FormBorderStyle = FormBorderStyle.FixedToolWindow

                Dim Txtbox As New RichTextBox()
                Txtbox.ScrollBars = ScrollBars.Both
                Txtbox.Multiline = True
                Txtbox.WordWrap = False
                Txtbox.Parent = form
                Txtbox.Dock = DockStyle.Fill
                Txtbox.Text = dbc
                windowService.ShowDialog(form)
            End Using
        End Sub

        Public Shared Sub TestShowVisualizer(Dataobject As Object)
            Dim host As New VisualizerDevelopmentHost(Dataobject, GetType(DBCommandVisualizer), GetType(DBcommandVisualizerSource))
            host.ShowVisualizer()
        End Sub


    End Class

    Public Class DBcommandVisualizerSource
        Inherits VisualizerObjectSource

        Public Overrides Sub GetData(target As Object, outgoingData As System.IO.Stream)
            Dim strScript As String = ""
            Dim strType As String = ""
            Dim dbc As DbCommand = DirectCast(target, DbCommand)
            Dim StrParams As String = ""
            Dim StrValues As String = ""
            If dbc.Parameters.Count > 0 Then
                For Each param As DbParameter In dbc.Parameters
                    strType = GetSqlDatabaseType(param)
                    strScript &= "DECLARE " & param.ParameterName & " " & strType & vbNewLine
                    StrParams &= "     " & param.ParameterName & " = " & param.ParameterName & ", " & vbNewLine
                    Try
                        If IsDBNull(param.Value) Then
                            StrValues &= "SET " & param.ParameterName & " = NULL" & vbNewLine
                        ElseIf IsNumeric(param.Value.ToString) = True Then
                            StrValues &= "SET " & param.ParameterName & " = " & param.Value.ToString & vbNewLine
                        ElseIf strType = "BIT" Then
                            StrValues &= "SET " & param.ParameterName & " = " & IIf(param.Value = True, "1", "0") & vbNewLine
                        ElseIf strType.Contains("DATE") Then
                            StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString("dd/MMMM/yyyy HH:mm:ss AM/PM") & "' " & vbNewLine
                        Else
                            StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString & "' " & vbNewLine
                        End If
                    Catch ex As Exception
                        StrValues &= "SET " & param.ParameterName & " = '" & param.Value.ToString & "' --Problem in value convertion Verify this value" & vbNewLine
                    End Try
                Next
                strScript &= StrValues
            End If
            If dbc.CommandType = CommandType.Text Then
                strScript &= dbc.CommandText & vbNewLine
            ElseIf dbc.CommandType = CommandType.StoredProcedure Then
                strScript &= "EXEC " & dbc.CommandText & vbNewLine
                If StrParams.Length > 0 Then strScript &= StrParams.Substring(0, StrParams.Length - 4)
            Else
                strScript &= "SELECT * FROM " & dbc.CommandText
            End If
            Dim writer = New StreamWriter(outgoingData)
            writer.WriteLine(strScript)
            writer.Flush()
        End Sub

        Private Function GetSqlDatabaseType(DParam As DbParameter) As String
            Dim param As New SqlClient.SqlParameter
            Try
                param.DbType = DParam.DbType
            Catch ex As Exception
                ' msgbox ex.message
            End Try
            Dim res As String = param.SqlDbType.ToString.ToUpper
            If res.Contains("VARCHAR") Then
                res &= "(" & IIf(DParam.Size > 4000, "MAX", IIf(DParam.Size < 10, "10", DParam.Size.ToString)) & ")"
            End If
            Return res
        End Function
    End Class

End Namespace